/*
 * Copyright (C) 2016 Baidu, Inc. All Rights Reserved.
 */
package net.sf.sqlparser.parser;

import java.text.MessageFormat;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.sqlparser.util.SQLParserUtils;
import net.sf.sqlparser.visitor.SQLParserNodeVisitor;
import net.sf.sqlparser.visitor.VisitorContext;

import org.apache.log4j.Logger;
import org.eclipse.swt.SWT;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.graphics.Font;
import org.eclipse.swt.graphics.FontData;
import org.eclipse.swt.graphics.Image;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Text;
import org.eclipse.swt.widgets.Tree;
import org.eclipse.swt.widgets.TreeItem;

/**
 * SqlParser小工具.
 * @author lixiangyang
 *
 */
public class SQLParserTool {
	
	/**
	 * 日志.
	 */
	private static Logger log = Logger.getLogger(SQLParserTool.class);
	
	/**
	 * Display.
	 */
	private static Display display;
	
	/**
	 * Shell.
	 */
	private Shell shell;
	
	/**
	 * 解析树Tree.
	 */
	private Tree tree;
	
	/**
	 * 解析时间.
	 */
	private Label lblParserTime;
    private  String initSql;
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		String  initSql = "SELECT P.NAME AS A, P.CHN_NAME FROM RC_ENTITY E, RC_ENTITY_PROP P"
								+ " WHERE E.ENTITY_ID = P.ENTITY_ID AND E.ENTITY_ID = '123'";
		initSql = "SELECT P.PROP_NAME, P.PROP_TYPE FROM RC_ENTTIY_PROP P WHERE P.ENTITY_ID IN"
								+ "(SELECT E.ENTITY_ID FROM RC_ENTITY E WHERE E.ENTITY_NAME = 'BZBM')";
		initSql = "select Distinct '\"@*&!~!@#$%^<>,./?\\|}{[]&*()' as Col1,123 as Col2,UserName, b.OrgId as CompanyID\r\n"
				+ " from dbo.User a\r\n"
				+ " inner join Org b on a.OrgId=b.OrgId Or (a.ccc=12 and a.Col1=b.Col2)\r\n"
				+ " left OUter joIN cqw on c.aa between 1 and 3\r\n"
				+ " where a=1 and not (b=1 or b=2) or (c=1 and (d in (1,2)))\r\n"
				+ " group by aaa, bbb, ccc  having a=1\r\n"
				+ " order by aaa, ccc asc, bbb desc, ddd asc, eee\r\n";
//		initSql = "select case when dummy = 'X' then 'XXX' else 'el' || 'se' end from dual";
//		initSql = "select dummy from dual where dummy like '%X%' and not dummy like '%A%'";
		StringBuffer var1 = new StringBuffer();
		var1.append("SELECT W0.zd8032 AS ZD8032, W0.zd8033 AS ZD8033, W0.dxmc AS DXMC, \n");
		var1.append("       W0.compid AS BZJCZZ_F_OBJID, W0.dxid AS DXID, W0.zd8031 AS ZD8031, \n");
		var1.append("       W0.zd8029 AS ZD8029, W0.zd8034 AS ZH1_1_F_OBJID, \n");
		var1.append("       ( W0.dxid ) AS F_OBJID, ( W1.dxid ) AS F_OBJID, ( W0.dxmc ) AS F_OBJNAME, ( W1.dxid ) AS F_OBJNAME \n");
		var1.append("FROM   (xtgldx8322) W0, (xtgldx8325) W1 \n");
		var1.append("WHERE  ( ( W1.zd8029 <> '40020313' ) AND W0.compid = '0000' AND ROWNUM <= 22 ) AND W0.zd8034 = W1.dxid ");
		initSql = var1.toString();
		final String tt = initSql;
//		initSql = "SELECT ${BZBM.BMID}, ${BZBM.BMMC} FROM (${BZBM}) WHERE ${BZBM.BMCODE} = '1001'";

		final SQLParserTool sqlParserTool = new SQLParserTool();

	 	sqlParserTool.showTree(initSql);

	}
	
	/**
	 * 显示SQL解析树.
	 * @param sql 初始SQL语句.
	 */
	private   void showTree(final String sql) {

        display=new Display();

        final GridLayout gridLayout = new GridLayout();
		gridLayout.numColumns = 3;
		shell = new Shell(display, SWT.SHELL_TRIM);
		shell.setLayout(gridLayout);
		//shell.setText("SQL解析(Parser By JSqlParser V7.0CN)　　　　My Blog: http://lxy19791111.iteye.com");
		shell.setText("SQL解析(Parser By JSqlParser V7.0CN)");
		final Image image = new Image(Display.getCurrent(), this.getClass().getResourceAsStream("/icons/sql_3.ico"));
		shell.setImage(image); 
		
		// 1.SQL内容
		final Text txtSql = new Text(shell, SWT.BORDER|SWT.V_SCROLL|SWT.H_SCROLL);
		txtSql.setText(sql);		
		final GridData txtGridData = new GridData(GridData.HORIZONTAL_ALIGN_FILL);
		txtGridData.heightHint = txtSql.getLineHeight() * 10;
		txtGridData.horizontalSpan = 3;
		txtSql.setLayoutData(txtGridData);
		
		// 2.解析按钮
		final Button btnParser = new Button(shell, SWT.PUSH);
		btnParser.setText("解析SQL");
		final GridData btnGridData = new GridData(GridData.CENTER);
		btnGridData.widthHint = 75;
		btnParser.setLayoutData(btnGridData);
		
		// 3.清除SQL按钮
		final Button btnClear = new Button(shell, SWT.PUSH);
		btnClear.setText("清除SQL");
		final GridData btnClearGridData = new GridData(GridData.CENTER);
		btnClearGridData.widthHint = 75;
		btnClear.setLayoutData(btnClearGridData);
		
		lblParserTime = new Label(shell, SWT.PUSH);
		final GridData lblGridData = new GridData(GridData.VERTICAL_ALIGN_CENTER);
		lblParserTime.setLayoutData(lblGridData);
		
		// 4.SQL解析树
		tree = new Tree(shell, SWT.BORDER);
		final GridData treeGridData = new GridData(GridData.FILL_BOTH);
		treeGridData.horizontalSpan = 3;
		tree.setLayoutData(treeGridData);
		
		// 解析按钮点击事件
		btnParser.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				final String sql = txtSql.getText();
				if (sql == null || sql.trim().length() == 0) {
					showMessageBox("SQL语句不能为空，请检查。");
					txtSql.forceFocus();
					return;
				}
				
				btnParserClick(sql);
			}
		});
		
		// 清除SQL按钮点击事件
		btnClear.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				txtSql.setText("");
				txtSql.forceFocus();
				tree.removeAll();
			}
		});
		
		// 初始时解析SQL
		btnParserClick(txtSql.getText());
		
		shell.open ();
		while (!shell.isDisposed()) {
			if (!display.readAndDispatch ()) display.sleep ();
		}
		display.dispose ();
	}
	
	/**
	 * 解析按钮点击事件.
	 * @param sql SQL语句.
	 * @throws Exception 异常 .
	 */
	private void btnParserClick(final String sql) {
		try {
			// 初始深度0
			int depth = 0;
			tree.removeAll();
			final TreeItem rootTree = new TreeItem(tree, 0);
						
			// 解析SQL语句
			final long beginTime = System.currentTimeMillis();
			final SimpleNode rootNode = parserSql(sql);
			final long endTime = System.currentTimeMillis();
			final String message = "本次解析耗时" + (endTime - beginTime) + "毫秒";
			lblParserTime.setText(message);
			
			// 使用访问者模式打印所有Table
			final VisitorContext ctx = new VisitorContext();
			final SQLParserNodeVisitor visitor = new SQLParserNodeVisitor(ctx);
			rootNode.jjtAccept(visitor, null);
			log.debug(message);
			
			// 设置Label的样式
			lblParserTime.setForeground(display.getSystemColor(SWT.COLOR_RED));			
			final FontData fontData = lblParserTime.getFont().getFontData()[0];
			fontData.setStyle(SWT.BOLD);
			final Font font = new Font(display, fontData);
			lblParserTime.setFont(font);
			
			setTreeText(rootTree, rootNode);
			buildTree(rootTree, rootNode, depth);
			rootTree.setExpanded(true);
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			showMessageBox("SQL语句不合法，请检查。");
		}
	}
	
	
	/**
	 * 生成树.
	 * @param parentTreeItem SWT上级树节点.
	 * @param parentNode 上级树节点.
	 * @param depth 树节点深度 .
	 */
	private void buildTree(final TreeItem parentTreeItem, final SimpleNode parentNode, int depth) {
		TreeItem childTreeItem;
		SimpleNode childNode;
		depth++;
		for (int i = 0; i< parentNode.jjtGetNumChildren(); i++) {
			childTreeItem = new TreeItem(parentTreeItem, 0);
			childNode = (SimpleNode)parentNode.jjtGetChild(i);
			setTreeText(childTreeItem, childNode);
			buildTree(childTreeItem, childNode, depth);
			
			// 只展开前面4层
			if (depth < 4) {
				childTreeItem.setExpanded(true);
			}
		}
	}
	
	/**
	 * 设置树节点内容.
	 * @param curTreeItem 当前SWT树节点.
	 * @param curNode 当前节点.
	 */
	private void setTreeText(final TreeItem curTreeItem, final SimpleNode curNode) {
		final Object value = curNode.jjtGetValue();
		if (value != null) {
			if (value instanceof Statement || value instanceof SelectBody) {
				curTreeItem.setText(MessageFormat.format("{1} - [{0}]",
						new Object[] { getSimpleName(value.getClass()), curNode.toString()}));
			} else {
				curTreeItem.setText(MessageFormat.format("{1} - [{0}] - {2}",
						new Object[] { getSimpleName(value.getClass()), curNode.toString(), value.toString()}));
			}
		} else {
			curTreeItem.setText(curNode.toString());
		}
	}
	
	/**
	 * 解析SQL语句.
	 * @param sql SQL语句 .
	 * @return 返回SQL树节点.
	 * @throws JSQLParserException 解析异常.
	 */
	private SimpleNode parserSql(final String sql) throws JSQLParserException {
		// 解析SQL语句
		final SQLParserResult result = SQLParserUtils.parseWithTree(sql);
		return result.getStatementNode();
	}
	
	/**
	 * 显示消息提示框 .
	 * @param message 消息内容. 
	 */
	private void showMessageBox(String message) {
		final MessageBox messageBox = new MessageBox(shell, SWT.OK);
		messageBox.setText("SQL解析");
		messageBox.setMessage(message);
		messageBox.open();
	}
	
	/**
	 * 获取Class对应的简单类名.
	 * @param clazz Class对象.
	 * @return 简单类名.
	 */
	private String getSimpleName(final Class clazz) {
		String simpleName = null;
		if (clazz != null) {
			final String className = clazz.getName();
			final int index = className.lastIndexOf(".");
			if (index > -1) {
				simpleName = className.substring(index + 1);
			} else {
				simpleName = className;
			}
		}
		
		return simpleName;
	}
}
